package com.eling.elcms.travel.dao.hibernate;

import com.eling.elcms.core.dao.hibernate.GenericDaoHibernate;
import com.eling.elcms.travel.dao.ICheckOutDealDao;
import com.eling.elcms.travel.model.CheckOutDeal;
import com.eling.elcms.travel.model.view.ChangeRoomSettlementView;
import org.springframework.stereotype.Repository;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

@Repository
public class CheckOutDealDaoHibernate extends GenericDaoHibernate<CheckOutDeal, Long> implements ICheckOutDealDao{

	public CheckOutDealDaoHibernate() {
		super(CheckOutDeal.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ChangeRoomSettlementView> queryChangeRoom(Long pkCIImplement) {
		StringBuffer sql = new StringBuffer();
		
		sql.append("  SELECT bp.idNumber,bp.name name1,CASE WHEN bp.sex='Male' THEN '男' ELSE '女' END,trt2.name name2,br2.`code` code1, "); 
		sql.append("  trt.`name` name3,br.`code` code2,tcr.changeRoomDate,bp.pkPersonalInfo ,tcr.pkChangeRoom                            ");
		sql.append("  FROM                                                                                                               ");
		sql.append("   travel_nonmembergroup tng                                                                                         ");
		sql.append("    JOIN travel_nonmemgroupreserve tngr                                                                              ");
		sql.append("   ON tng.pkNonMemberGroup=tngr.pkNonMemberGroup                                                                     ");
		sql.append("    JOIN travel_checkinimplement tci                                                                                 ");
		sql.append("   ON tci.pkNonMemGroupReserve=tngr.pkNonMemGroupReserve                                                             ");
		sql.append("  JOIN travel_nonmemberregister tnmr ON tnmr.pkNonMemberGroup = tng.pkNonMemberGroup                                 ");
		sql.append("  JOIN travel_signupregister tsr                                                                                     ");
		sql.append("  ON tsr.pkSignUpRegister=tnmr.pkSignUpRegister                                                                      ");
		sql.append("    JOIN travel_roompersonalInfo trp                                                                                 ");
		sql.append("  ON trp.pkCIImplement=tci.pkCIImplement                                                                             ");
		sql.append("   JOIN bd_personalinfo bp                                                                                           ");
		sql.append("  ON bp.pkPersonalInfo=tsr.pkPersonalInfo                                                                            ");
		sql.append("   JOIN travel_changeroom tcr                                                                                        ");
		sql.append("  ON tcr.pkPersonalInfo=bp.pkPersonalInfo                                                                            ");
		sql.append("   JOIN bd_room br                                                                                                   ");
		sql.append("  ON br.pkRoom=tcr.pkRoom                                                                                            ");
		sql.append("   JOIN travel_roomtype trt                                                                                          ");
		sql.append("  ON trt.pkRoomType=br.pkRoomType                                                                                    ");
		sql.append("  JOIN travel_roomregister trr                                                                                       ");
		sql.append("  ON trr.pkRoomRegister=trp.pkRoomRegister                                                                           ");
		sql.append("  JOIN bd_room br2                                                                                                   ");
		sql.append("  ON trr.pkRoom=br2.pkRoom                                                                                           ");
		sql.append("  JOIN travel_roomtype trt2                                                                                          ");
		sql.append("  ON trt2.pkRoomType=br2.pkRoomType                                                                                  ");
		sql.append("   WHERE tci.pkCIImplement=:pkCIImplement                                                                            ");
		sql.append(" GROUP BY                                                                                                            ");
		sql.append("        tcr.pkChangeRoom                                                                                             ");
		sql.append("  ORDER BY tcr.changeRoomDate                                                                                        ");
		List<Object[]> objList = getSession().createNativeQuery(sql.toString()).setParameter("pkCIImplement", pkCIImplement).list();
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		List<ChangeRoomSettlementView> viewList = new ArrayList<>();
		Long lastPk1=0l;
		Long lastPk2=0l;
		String lastRoomType1 = "";
		String lastRoomCode1 = "";
		String lastRoomType2 = "";
		String lastRoomCode2 = "";
		for (int i = 0; i < objList.size(); i++) {
			ChangeRoomSettlementView changeRoomSettlView = new ChangeRoomSettlementView();
			Object[] objects = objList.get(i);
			changeRoomSettlView.setIdNumber(objects[0]==null?"":objects[0].toString());
			changeRoomSettlView.setName(objects[1]==null?"":objects[1].toString());
			changeRoomSettlView.setSex(objects[2]==null?"":objects[2].toString());
			changeRoomSettlView.setRoomType(objects[3]==null?"":objects[3].toString());
			changeRoomSettlView.setRoomCode(objects[4]==null?"":objects[4].toString());
			changeRoomSettlView.setChangeRoomType(objects[5]==null?"":objects[5].toString());
			changeRoomSettlView.setChangeRoomCode(objects[6]==null?"":objects[6].toString());
			try {
				changeRoomSettlView.setChangeRoomDate(objects[7]==null?null:df.parse(objects[7].toString()));
			} catch (ParseException e) {
				e.printStackTrace();
			}
			if(i==0){
				lastPk1 = (objects[8]==null?0l:Long.parseLong(objects[8].toString()));
				lastRoomType1 = (objects[5]==null?"":objects[5].toString());
				lastRoomCode1 = (objects[6]==null?"":objects[6].toString());
			}else if(lastPk1!=Long.parseLong(objects[8].toString())&&lastPk2==0l){
				lastPk2 = (objects[8]==null?0l:Long.parseLong(objects[8].toString()));
				lastRoomType2 = (objects[5]==null?"":objects[5].toString());
				lastRoomCode2 = (objects[6]==null?"":objects[6].toString());
			}else if(lastPk1==Long.parseLong(objects[8].toString())){
				changeRoomSettlView.setRoomCode(lastRoomCode1);
				changeRoomSettlView.setRoomType(lastRoomType1);
				lastRoomType1 = (objects[5]==null?"":objects[5].toString());
				lastRoomCode1 = (objects[6]==null?"":objects[6].toString());
			}else {
				changeRoomSettlView.setRoomCode(lastRoomCode2);
				changeRoomSettlView.setRoomType(lastRoomType2);
				lastRoomType2 = (objects[5]==null?"":objects[5].toString());
				lastRoomCode2 = (objects[6]==null?"":objects[6].toString());
			}
			viewList.add(changeRoomSettlView);
		}
		return viewList;
	}

}
